package io.renren.modules.sp.service.impl;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.core.toolkit.Constants;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import io.renren.common.redis.RedisUtils;
import io.renren.common.service.impl.CrudServiceImpl;
import io.renren.common.utils.DateUtils;
import io.renren.modules.security.user.UserDetail;
import io.renren.modules.sp.dao.SpMatchItemTeamDao;
import io.renren.modules.sp.dao.SpMatchItemTeamUserDao;
import io.renren.modules.sp.dto.SpMatchItemTeamUserDTO;
import io.renren.modules.sp.entity.SpMatchItemTeamUserEntity;
import io.renren.modules.sp.service.SpMatchItemTeamUserService;
import io.renren.modules.sys.dao.SysCommDao;
import io.renren.modules.sys.service.SysUserService;
import org.apache.commons.lang3.StringUtils;
import org.apache.ibatis.annotations.Param;
import org.apache.shiro.SecurityUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 参赛成员
 *
 * @author whc 15953295779@126.com
 * @since 3.0 2023-04-21
 */
@Service
public class SpMatchItemTeamUserServiceImpl extends CrudServiceImpl<SpMatchItemTeamUserDao, SpMatchItemTeamUserEntity, SpMatchItemTeamUserDTO> implements SpMatchItemTeamUserService {
    @Autowired
    private SysCommDao sysCommDao;
    @Autowired
    private RedisUtils redisUtils;
    @Autowired
    private SpMatchItemTeamDao spMatchItemTeamDao;
    @Autowired
    private SysUserService sysUserService;

    @Override
    public QueryWrapper<SpMatchItemTeamUserEntity> getWrapper(Map<String, Object> params){
        QueryWrapper<SpMatchItemTeamUserEntity> wrapper = new QueryWrapper<>();

        String matchItemTeamId = (String)params.get("matchItemTeamId");
        wrapper.eq(StringUtils.isNotBlank(matchItemTeamId), "match_item_team_id", matchItemTeamId);
        String groupId = (String)params.get("groupId");
        wrapper.eq(StringUtils.isNotBlank(groupId), "group_id", groupId);
        return wrapper;
    }

    @Override
    public String queryTicketByUserId(Long matchId, Long userId){
        SpMatchItemTeamUserEntity entity = this.baseDao.selectOne(
                new QueryWrapper<SpMatchItemTeamUserEntity>()
                        .eq("match_id", matchId)
                        .eq("user_id", userId)
        );
        String pic = entity.getCertificateUrl();
        if(StringUtils.isNotEmpty(pic)){
            return pic;
        }else{
            return "";
        }
    }

    @Override
    public List<SpMatchItemTeamUserEntity> queryTicketBy(QueryWrapper<SpMatchItemTeamUserEntity> certificate){
        return this.baseDao.selectList(certificate);
    }

    @Override
    public void updCertificate(SpMatchItemTeamUserEntity entity){
        this.baseDao.updateById(entity);
    }
    /**
    *查询报表
     * @date 2023/5/8 10:04
    */
    public  List<Map<String, String>> queryReport(String myMatchId,String mygroupId,String myprovince,String mymatchItemId
            ,String type){

        String groupName = "";
        String groupName2 = "";
        if("2".equals(type)){
            groupName = ",sd.dict_label groupName";
            groupName2 = ",sd.dict_label";
        }
        String where = "";
        if(StringUtils.isNotEmpty(myMatchId)){
            where += " and sm.id = "+myMatchId+"";
        }
        if (StringUtils.isNotEmpty(mygroupId)){
            where += " and st.group_id in ("+mygroupId+") ";
        }
        if (StringUtils.isNotEmpty(myprovince)){
            myprovince = myprovince.replace(",","','");
            where += " and ta.code in ('"+myprovince+"') ";
        }
        if (StringUtils.isNotEmpty(mymatchItemId)){
            where += " and si.id in ("+mymatchItemId+" )";
        }
        String sql ="\n" +
                "select bi.item_name typeName ,ta.name topName  ,format(count(1),0) num "+groupName+" from sp_match_item_team_user t\n" +
                "left join  sp_match_item_team st on t.match_item_team_id = st.id\n" +
                "left join sp_match_item si on si.id = st.match_item_id\n" +
                "left join sp_match sm on sm.id = si.match_id\n" +
                "left join sp_base_match_item bi on bi.id = si.base_match_item_id\n" +
                "left join sp_team bt  on bt.id = st.team_id\n" +
                "left join t_area ta on ta.code = bt.province and  ta.pcode = '0'\n" +
                "left join sys_dict_data sd on sd.id = st.group_id\n" +
                " where t.type = 1 and st.status in (1,2,4,5,6) "+ where+" "+
                "group by bi.item_name ,ta.name \n" + groupName2+
                " order by bi.sort asc ";
        Map<String,Object> param = new HashMap<>();
        List<Map<String, String>> result =  sysCommDao.commList(sql,param);
        return result;
    }
    /**
    *统计参赛人数报表
    */
    public  List<Map<String, String>> querySignReport(String matchName ,Long matchLevel,String startSignDate ,String endSignDate ,Long province,Long city ){
        StringBuffer whereSql =new  StringBuffer();
        Map<String,Object> param = new HashMap<>();
        UserDetail user = (UserDetail) SecurityUtils.getSubject().getPrincipal();

        String season = (String)redisUtils.get("season-" + user.getId());
        if(StringUtils.isNotEmpty(matchName)){
            whereSql.append(" and t4.match_name LIKE #{p.matchName}");
            param.put("matchName","%"+matchName+"%");
        }
        if(StringUtils.isNotEmpty(season)){
            whereSql.append(" and t4.season = #{p.season}");
            param.put("season",season);
        }
        if(matchLevel!=null){
            whereSql.append(" and t4.match_level =#{p.matchLevel}");
            param.put("matchLevel",matchLevel);
        }
        if(province!=null){
            whereSql.append(" and t4.province =#{p.province}");
            param.put("province",province);
        }
        if(city!=null){
            whereSql.append(" and t4.city =#{p.city}");
            param.put("city",city);
        }
        if(StringUtils.isNotEmpty(startSignDate)){
            whereSql.append(" and t4.begin_signup >= #{p.startSignDate}");
            param.put("startSignDate", DateUtils.parse(startSignDate,DateUtils.DATE_FORMAT_YEAR_MONTH));
        }
        if(StringUtils.isNotEmpty(endSignDate)){
            whereSql.append(" and t4.end_signup <= #{p.endSignDate}");
            param.put("endSignDate",DateUtils.parse(startSignDate+" 23:59:59",DateUtils.DATE_TIME_PATTERN));
        }

        // 权限过滤
        if(user.getDeptId()!=null) {
            // 判断管理员的角色
            String userType = sysUserService.getSysUserType();
            //如果是赛项管理员
            if ("2".equals(userType)) {
                whereSql.append(" and t4.admin_item_id = #{p.adminItemId}");
                param.put("adminItemId", user.getId());
            }
            //如果是赛事管理员
            if ("1".equals(userType) || "3".equals(userType) || "4".equals(userType)) {
                whereSql.append(" and t4.admin_id = #{p.adminId}");
                param.put("adminId", user.getId());
            }
            // 打分员
            if ("5".equals(userType)) {
                if(user.getMatchId()!=null) {
                    whereSql.append(" and t4.id = #{p.id}");
                    param.put("id", user.getMatchId());
                }
            }
        }

        String sql =" select a.item_name  typeName ,a.match_name topName,format(a.qty,0) num from ( " +
                "select   t5.item_name item_name , t4.match_name match_name ,count(1) qty from sp_match_item_team_user t " +
                "left join sp_match_item_team t2 on t.match_item_team_id = t2.id " +
                "left join sp_match_item t3 on t3.id = t2.match_item_id " +
                "inner join sp_match t4 on t4.id = t3.match_id " +
                "inner join sp_base_match_item t5 on t5.id = t3.base_match_item_id " +
                "where t.type ='1' " +
                whereSql.toString()+
                " and t2.status in (1,2,4,5,6) " +
                "group  by t5.item_name , t4.match_name " +
                ") a order by a.match_name , a.item_name desc ";



        List<Map<String, String>> result =  sysCommDao.commList(sql,param);
        return result;
    }

    /**
     * 导出报名信息
     * */
    @Override
    public List<Map<String,String>> queryTicketByMatch(Long matchId, List<Long> itemList){

       /* String sql = "select DISTINCT bsm.item_name ,g.dict_label,u.real_name,SUBSTR(st.ticket, 4) ticket  \n" +
                "from sp_match sm \n" +
                "left join sp_match_item smi on smi.match_id = sm.id \n" +
                "left join sp_base_match_item bsm on bsm.id = smi.base_match_item_id\n" +
                "left join sp_match_item_team mt on mt.match_item_id = smi.id \n" +
                "left join sp_match_item_team_user mu on mu.match_item_team_id = mt.id \n" +
                "inner join sp_ticket st on st.user_id  = mu.user_id and st.match_id = sm.id \n" +
                "inner join sp_user u on u.id = mu.user_id \n" +
                "inner join sys_dict_data g on g.id = mt.group_id\n" +
                "where mu.status in (1,2,4,5,6)  and mu.type=1 " +
                whereSql.toString()+
                " order by bsm.item_name ,g.dict_label, mu.create_date ";
        System.out.println(sql);*/

        Map<String, Object> params = new HashMap<>();
        params.put("limit", "50000");
        Page page = createPage(params);
        params.put("matchId", matchId);
        params.put("orderField", "order by bi.sort,  bi.item_name, dd.dict_label, tu.id");
        IPage<Map<String,Object>> page2 = spMatchItemTeamDao.signRecordStudent(page, params);
        List<Map<String,String>> result = new ArrayList<>();
        Map<String,String> map2 = new HashMap<>();
        for (Map<String, Object> map : page2.getRecords()) {
            map2 = new HashMap<>();
            map2.put("item_name",map.get("item").toString());
            map2.put("dict_label",map.get("dictLabel").toString());
            map2.put("real_name",map.get("realName").toString());
            map2.put("ticket",map.get("ticket").toString());
            result.add(map2);
        }
        return result;
    }

    @Override
    public List<SpMatchItemTeamUserEntity> queryList(QueryWrapper<SpMatchItemTeamUserEntity> wrapper){
        return this.baseDao.selectList(wrapper);
    }

    @Override
    public List<Map<String, Object>> findAwardStatisticsByMatchId(Map<String,Object> param) {
        return this.baseDao.findAwardStatisticsByMatchId(param);
    }
}
